﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.CompilerServices;

namespace ADO数据库编程
{

    public class Person
    {
        public long ID { get; set; }
        public string Name { get; set; }
        public string Addr { get; set; }
        public string Sex { get; set; }
        public int Age { get; set; }

        public override string ToString()
        {
            return $"id:{ID},name:{Name},age:{Age},sex:{Sex},addr:{Addr}";
        }
    }

    public class SqlDataAdapterDemo
    {
        public void testSelect()
        {
            DataTable dt = createDt();
            showDt(dt);
        }

        public void testInsert()
        {

            SqlConnection sqlConnection = SqlHelper.CreateConn();
            using (sqlConnection)
            {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

                string sql = " select id,name,age,sex,addr from person order by id asc ";
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                sqlDataAdapter.SelectCommand = sqlCommand;
                
                sql = "INSERT INTO person (name,age,sex,addr) values(@name,@age,@sex,@addr)";
                SqlCommand sqlCommand1 = new SqlCommand(sql,sqlConnection);
                sqlDataAdapter.InsertCommand = sqlCommand1;

                SqlParameter p1 = new SqlParameter("@id", SqlDbType.BigInt, 11, "id");
                SqlParameter p2 = new SqlParameter("@name", SqlDbType.VarChar, 10, "name");
                SqlParameter p3 = new SqlParameter("@sex", SqlDbType.VarChar, 10, "sex");
                SqlParameter p4 = new SqlParameter("@addr", SqlDbType.VarChar, 10, "addr");
                SqlParameter p5 = new SqlParameter("@age", SqlDbType.Int, 10, "age");
                sqlDataAdapter.InsertCommand.Parameters.Add(p1);
                sqlDataAdapter.InsertCommand.Parameters.Add(p2);
                sqlDataAdapter.InsertCommand.Parameters.Add(p3);
                sqlDataAdapter.InsertCommand.Parameters.Add(p4);
                sqlDataAdapter.InsertCommand.Parameters.Add(p5);

                // DataTable 是一个内存数据表，对应数据库中的一张表。
                // DataSet 是一个数据集，可以包含多个DataTable。
                DataTable dt = new DataTable("Person");
                sqlDataAdapter.Fill(dt);

                // 添加
                DataRow dr = dt.NewRow();
                dr["name"] = "周星驰";
                dr["age"] = 60;
                dr["sex"] = "男";
                dr["addr"] = "香港";
                dt.Rows.Add(dr);

                sqlDataAdapter.Update(dt);

                Console.WriteLine("插入成功");
            }
        }



        public void testUpdate()
        {
            SqlConnection sqlConnection = SqlHelper.CreateConn();
            using (sqlConnection)
            {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

                string sql = " select id,name,age,sex,addr from person ";
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);

                sqlDataAdapter.SelectCommand = sqlCommand;

                // 创建一个更新命令  
                sqlDataAdapter.UpdateCommand =
                    new SqlCommand("UPDATE person SET name = @name , sex = @sex ,addr = @addr,age = @age WHERE id = @id", sqlConnection);

                SqlParameter p1 = new SqlParameter("@id", SqlDbType.BigInt,11,"id");
                SqlParameter p2 = new SqlParameter("@name", SqlDbType.VarChar,10,"name");
                SqlParameter p3 = new SqlParameter("@sex", SqlDbType.VarChar, 10, "sex");
                SqlParameter p4 = new SqlParameter("@addr", SqlDbType.VarChar, 10, "addr");
                SqlParameter p5 = new SqlParameter("@age", SqlDbType.Int, 10, "age");
                sqlDataAdapter.UpdateCommand.Parameters.Add(p1);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p2);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p3);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p4);
                sqlDataAdapter.UpdateCommand.Parameters.Add(p5);

                DataSet ds2 = new DataSet("模拟数据集2");
                DataTable dt2 = new DataTable("person");
                ds2.Tables.Add(dt2);
                sqlDataAdapter.Fill(dt2);

                foreach(DataRow drow in dt2.Rows)
                {
                    long id = (long)drow[0];
                    if(id == 5)
                    {
                        drow["name"] = "郑伊健";
                        drow["sex"] = "男";
                        drow["addr"] = "香港";
                        drow["age"] = 55;
                    }
                }
                sqlDataAdapter.Update(ds2, "person");
                Console.WriteLine("更新数据成功");
                ds2.Tables[0].Clear();


            }
        }

        public void testDelete()
        {
            SqlConnection sqlConnection = SqlHelper.CreateConn();
            using (sqlConnection)
            {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();

                string sql = " select id,name,age,sex,addr from person ";
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);

                sqlDataAdapter.SelectCommand = sqlCommand;

                // 创建一个更新命令  
                sqlDataAdapter.DeleteCommand =
                    new SqlCommand("Delete from person WHERE id = @id", sqlConnection);

                SqlParameter p1 = new SqlParameter("@id", SqlDbType.BigInt, 11, "id");
                sqlDataAdapter.DeleteCommand.Parameters.Add(p1);

                DataSet ds2 = new DataSet("模拟数据集2");
                DataTable dt2 = new DataTable("person");
                ds2.Tables.Add(dt2);
                sqlDataAdapter.Fill(dt2);

                foreach (DataRow drow in dt2.Rows)
                {
                    long id = (long)drow[0];
                    if (id == 6)
                    {
                        drow.Delete();
                    }
                }
                sqlDataAdapter.Update(ds2, "person");
                Console.WriteLine("更新数据成功");
            }
        }



        public void showDt(DataTable dt)
        {
            if(dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dtRow in dt.Rows)
                {
                    Console.WriteLine("{0},{1},{2},{3},{4}",
                        dtRow["id"], dtRow["name"], dtRow["age"], dtRow["sex"], dtRow["addr"]);
                }
            }
        }

        public SqlDataAdapter CreateAdapter(out DataTable dt)
        {
            string sql = " select id,name,age,sex,addr from person order by id asc ";
            SqlCommand sqlCommand = SqlHelper.createSqlCommand(sql, null);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            dt = new DataTable("Person");
            // 填充datatable
            sqlDataAdapter.Fill(dt);
            SqlCommandBuilder sqlcb = new SqlCommandBuilder(sqlDataAdapter);
            return sqlDataAdapter;
        }
        
        public DataTable createDt()
        {
            string sql = " select id,name,age,sex,addr from person order by id asc ";
            SqlCommand sqlCommand = SqlHelper.createSqlCommand(sql, null);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            DataTable dt = new DataTable("Person");
            sqlDataAdapter.Fill(dt);
            return dt;
        }
    }

    public class SqlHelper
    {
        public static String getConnStr(String key)
        {
            String connectionString = null;

            // 方法一：从配置文件中获取
            if (key == null) key = "connSqlServer";
            connectionString = ConfigurationManager.ConnectionStrings[key].ConnectionString;
            Console.WriteLine(connectionString);


            // 方法二：使用SqlConnectionStringBuilder构造 
            // Data Source=.;Initial Catalog=School;User Id=sa;Password=123456;
            //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            //builder.DataSource = "127.0.0.1";
            //builder.InitialCatalog = "School";
            //builder.UserID = "sa";
            //builder.Password = "123456";

            //connectionString = builder.ConnectionString;

            return connectionString;
        }

        public static SqlConnection CreateConn()
        {
            SqlConnection conn = new SqlConnection(getConnStr("connSqlServer2"));
            return conn;
        }

        public static SqlCommand createSqlCommand(string sql, Dictionary<string, object> dic)
        {
            SqlConnection conn = CreateConn();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = conn;
            sqlCommand.CommandText = sql;
            sqlCommand.CommandType = CommandType.Text;

            if (dic != null && dic.Count > 0)
            {
                foreach (KeyValuePair<string, object> pair in dic)
                {
                    string key = pair.Key;
                    object value = pair.Value;
                    sqlCommand.Parameters.Add(new SqlParameter(key, value));
                }
            }
            return sqlCommand;
        }
    }
}